/*
	exec sp_test_release_report_proc '0','0','0','0','0','0','DETAIL','XLREPORT','0'
	exec sp_test_release_report_proc '0','0','0','0','3/30/2009','5/18/2009','DETAIL','XLREPORT','0'
*/
CREATE proc sp_test_release_report_proc

@PROCESSSTEP_IDS	VARCHAR(200),
@VENDORID		VARCHAR(50),
@STATUS			VARCHAR(50),
@CATEGORY		VARCHAR(50),
@FROMDATE		VARCHAR(50),
@TODATE			VARCHAR(50),
@ISDETAIL		VARCHAR(50),
@REPORTTYPE		VARCHAR(50),
@DEFAULT_PROMPT		VARCHAR(50)

AS
BEGIN

DECLARE @SQLSTRING VARCHAR(4000)

DECLARE @RELEASE_CURSOR	CURSOR
DECLARE @WAFER_CURSOR	CURSOR
DECLARE @LOT_CURSOR	CURSOR

DECLARE @WAFER_IDS_TABLE TABLE ( LOT_ID INT, WAFER_ID INT )

DECLARE @RELEASE_ID INT
DECLARE @SHIPPED_LOT_NUMBER VARCHAR(50)
DECLARE @WAFER_ID  VARCHAR(10)
DECLARE @WAFER_IDS VARCHAR(200)
DECLARE @LOT_ID INT
DECLARE @LOT_NUMBER VARCHAR(50)
DECLARE @LOT_NUMBERS VARCHAR(200)
DECLARE @DB_INSTRUCTIONS VARCHAR(500)
DECLARE @LOTWAFER_IDS	VARCHAR(200)

CREATE TABLE #RELEASE_TABLE 
			([TEST REL NO] VARCHAR(50), [TEST REL DATE] DATETIME, [PO NO] VARCHAR(50),
			 [FROM VENDOR] VARCHAR(50), [TO VENDOR] VARCHAR(50), [PACKAGE TYPE] VARCHAR(50),
			 [REQUEST TYPE] VARCHAR(50), [TEST REVISION NO] VARCHAR(50),[STATUS] VARCHAR(50),
			 [SHIP TO VENDOR] VARCHAR(50), [PIN COUNT] VARCHAR(50), [INSTRUCTIONS] VARCHAR(500), 
			 /*[FAB LOT NUMBER] VARCHAR(200),*/[TEST LOT NUMBER] VARCHAR(50), [QTY PULLED] INT,
			 [PART NUMBER] VARCHAR(50) )

SET @SQLSTRING = N' INSERT INTO #RELEASE_TABLE
		SELECT 
			 TEST_REL_NO,min(TEST_REL_DATE),TEST_REL_PO,
			( SELECT V_NAME FROM VENDOR WHERE VENDOR_ID = FROM_VENDOR_ID ),
			( SELECT V_NAME FROM VENDOR WHERE VENDOR_ID = TO_VENDOR_ID ),
			PACKAGE_TYPE,RELEASE_TYPE,TEST_REL_REVISION_NO,TEST_REL_STATUS,
			( SELECT V_NAME FROM VENDOR WHERE VENDOR_ID = SHIP_TO_VENDOR_ID ),PIN_COUNT,
			 /*null,*/ NULL, ISNULL(shipped_lot_number, LOT_NUMBER),sum(QTY_PULLED),P.PART_NUMBER
		   
		FROM 
			TEST_RELEASE_TABLE TRT, TEST_RELEASE_LOT_TABLE TRLT,PRODUCT P
		WHERE 
			TRT.TEST_REL_ID = TRLT.TEST_REL_ID
			AND TRT.FROM_PROCESSSTEP_ID = P.PROCESSSTEP_ID '

IF @PROCESSSTEP_IDS IS NOT NULL AND @PROCESSSTEP_IDS <> '0'
BEGIN
	SET @SQLSTRING = @SQLSTRING + ' AND TRT.FROM_PROCESSSTEP_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ( '''+ @PROCESSSTEP_IDS+''') )'
END

IF @VENDORID IS NOT NULL AND @VENDORID <> '0'
BEGIN
	SET @SQLSTRING = @SQLSTRING + ' AND TO_VENDOR_ID = '''+ convert(varchar,@VENDORID)+ ''''
END

IF @STATUS IS NOT NULL AND @STATUS <> '0'
BEGIN
	SET @SQLSTRING = @SQLSTRING + ' AND TEST_REL_STATUS = '''+ @STATUS+ ''''
END

IF @CATEGORY IS NOT NULL AND @CATEGORY <> '0'
BEGIN
	SET @SQLSTRING = @SQLSTRING + ' AND RELEASE_TYPE = '''+ @CATEGORY+ ''''
END

IF @FROMDATE IS NOT NULL AND @FROMDATE <> '0'
BEGIN
	SET @SQLSTRING = @SQLSTRING +' AND CONVERT(DATETIME,TEST_REL_DATE) >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
END

IF @TODATE IS NOT NULL AND @TODATE <> '0'
BEGIN
	SET @SQLSTRING = @SQLSTRING +' AND CONVERT(DATETIME,TEST_REL_DATE) <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
END

set @SQLSTRING = @SQLSTRING +' GROUP BY 
				TEST_REL_NO,shipped_lot_number,TEST_REL_PO,FROM_VENDOR_ID,TO_VENDOR_ID,
				PACKAGE_TYPE,RELEASE_TYPE,TEST_REL_REVISION_NO,TEST_REL_STATUS,SHIP_TO_VENDOR_ID,
				PIN_COUNT,P.PART_NUMBER,LOT_NUMBER,FROM_PROCESSSTEP_ID '
--print'@SQLSTRING :-'+@SQLSTRING
EXEC (@SQLSTRING)

--SELECT * FROM #RELEASE_TABLE
	
	/*SET @RELEASE_CURSOR = CURSOR FOR SELECT [ASSM REL ID], [ASSEMBLY LOT NUMBER]  FROM #RELEASE_TABLE
	OPEN @RELEASE_CURSOR
	FETCH NEXT FROM @RELEASE_CURSOR INTO @RELEASE_ID, @SHIPPED_LOT_NUMBER
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @DB_INSTRUCTIONS = DB_INSTRUCTIONS FROM ASSM_RELEASE_TABLE WHERE ASSEMBLY_RELEASE_ID = @RELEASE_ID
		
		IF @ISDETAIL = 'DETAIL'
		BEGIN
			SET @LOT_CURSOR = CURSOR FOR SELECT DISTINCT LOT_ID FROM ASSM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @RELEASE_ID AND Shipped_lot_Number = @SHIPPED_LOT_NUMBER

			OPEN @LOT_CURSOR
			FETCH NEXT FROM @LOT_CURSOR INTO @LOT_ID
			WHILE @@FETCH_STATUS = 0
			BEGIN
				DELETE FROM @WAFER_IDS_TABLE
				
				SET @WAFER_IDS = ''	
	
				INSERT INTO @WAFER_IDS_TABLE 
				SELECT LOT_ID,WAFER_ID FROM ASSM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @RELEASE_ID AND  Shipped_lot_Number = @SHIPPED_LOT_NUMBER

				SELECT TOP 1 @WAFER_ID = WAFER_ID FROM @WAFER_IDS_TABLE WHERE LOT_ID = @LOT_ID
				WHILE ( @@ROWCOUNT > 0 ) 
				BEGIN
					IF ( @WAFER_IDS = '' )
					BEGIN
						SET @WAFER_IDS = @WAFER_ID
					END
					ELSE
					BEGIN 
						SET @WAFER_IDS = @WAFER_IDS +','+ @WAFER_ID
					END
					
					DELETE FROM @WAFER_IDS_TABLE WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID  
											
					SELECT TOP 1 @WAFER_ID = WAFER_ID FROM @WAFER_IDS_TABLE WHERE LOT_ID = @LOT_ID
				END

				SELECT @LOT_NUMBER = FAB_LOT_NUMBER FROM LOT_TABLE WHERE LOT_ID = @LOT_ID
				
				IF @LOT_NUMBERS IS NULL
				BEGIN
					SET @LOT_NUMBERS = @LOT_NUMBER
					SET @LOTWAFER_IDS = @WAFER_IDS
				END
				ELSE
				BEGIN
					SET @LOT_NUMBERS = @LOT_NUMBERS + '/'+@LOT_NUMBER
					SET @LOTWAFER_IDS = @LOTWAFER_IDS +'/'+@WAFER_IDS
				END
				
			FETCH NEXT FROM @LOT_CURSOR INTO @LOT_ID
			END
			CLOSE @LOT_CURSOR
			DEALLOCATE @LOT_CURSOR

			UPDATE #RELEASE_TABLE 
				SET [WAFER ID] = @LOTWAFER_IDS, [FAB LOT NUMBER] = @LOT_NUMBERS,
				    [INSTRUCTIONS] = @DB_INSTRUCTIONS
			WHERE 
				[ASSM REL ID] = @RELEASE_ID AND [ASSEMBLY LOT NUMBER] = @SHIPPED_LOT_NUMBER
		END
		ELSE
		BEGIN
			
			UPDATE #RELEASE_TABLE 
				SET [INSTRUCTIONS] = @DB_INSTRUCTIONS, [FAB LOT NUMBER] = @LOT_NUMBER
			WHERE 
				[ASSM REL ID] = @RELEASE_ID
		END
		SET @LOT_NUMBER = NULL
		SET @WAFER_IDS = NULL
		SET @LOT_NUMBERS = NULL
		SET @LOTWAFER_IDS = NULL
		SET @DB_INSTRUCTIONS = NULL

		DELETE FROM @WAFER_IDS_TABLE

	FETCH NEXT FROM @RELEASE_CURSOR INTO @RELEASE_ID, @SHIPPED_LOT_NUMBER
	END
	CLOSE @RELEASE_CURSOR
	DEALLOCATE @RELEASE_CURSOR*/
	

	IF @REPORTTYPE = 'XLREPORT'
	BEGIN	
			SELECT 
				 [TEST REL DATE],[PART NUMBER],[TEST REL NO], [TEST REVISION NO],[STATUS], 
				 /*[FAB LOT NUMBER],*/[TEST LOT NUMBER],[QTY PULLED], [FROM VENDOR],[TO VENDOR],
				 [SHIP TO VENDOR], [PACKAGE TYPE], [PO NO],[REQUEST TYPE],
				 [PIN COUNT], [INSTRUCTIONS]
			FROM 
				#RELEASE_TABLE
			ORDER BY [TEST REL DATE],[TEST REL NO] DESC
		
	END
	

END
